package cn.edu.klmy.dao.impl;

import cn.edu.klmy.dao.AbstractJdbcTemplate;
import cn.edu.klmy.dao.IElectiveDao;
import cn.edu.klmy.models.course.Course;
import cn.edu.klmy.models.course.CourseStructure;
import cn.edu.klmy.models.elective.*;
import com.googlecode.ehcache.annotations.PartialCacheKey;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 顾伟
 *         创建日期：13-5-15
 */
@Repository
public class ElectiveDao extends AbstractJdbcTemplate implements IElectiveDao {
    public List<ElectiveCourseSituation> getCourseElection(int schedulingId, int term, int section) {
        String sql = "SELECT DISTINCT A.COURSE_SCHEDULING_ID,A.ELECTIVE_TIME,A.ELECTIVE_WEEK," +
                "(SELECT COUNT(H.STUDENT_ID) FROM STUDENT_COURSE_ELECTIVE H " +
                "WHERE H.COURSE_SCHEDULING_ID=A.COURSE_SCHEDULING_ID" +
                " AND H.ELECTIVE_TERM=" + term +
                " AND A.ELECTIVE_WEEK&H.ELECTIVE_WEEK=H.ELECTIVE_WEEK" +
                " AND A.ELECTIVE_TIME&H.ELECTIVE_TIME=H.ELECTIVE_TIME" +
                " AND H.ELECTIVE_SECTION=" + section +
                " AND YEAR(H.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "') AS CURRENT_NUM " +
                "FROM STUDENT_COURSE_ELECTIVE A " +
                "WHERE A.ELECTIVE_TERM=" + term +
                " AND A.ELECTIVE_SECTION=" + section +
                " AND YEAR(A.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'" +
                " AND A.COURSE_SCHEDULING_ID=" + schedulingId;
        return this.jdbcTemplate.query(sql, new ElectiveCourseSituationRowMapper());
    }

    public List<StudentCourseElective> getStudentCurrentElection(int studentId, int term, int section) {
        String sql = "SELECT A.ID,A.ELECTIVE_TIME,A.ELECTIVE_WEEK,B.COURSE_SCHEDULING_ID,C.COURSE_NAME,D.TEACHER_NAME,E.CLASSROOM_LOCATION FROM STUDENT_COURSE_ELECTIVE A" +
                " LEFT JOIN COURSE_SCHEDULING B ON A.COURSE_SCHEDULING_ID=B.COURSE_SCHEDULING_ID" +
                " LEFT JOIN COURSE C ON B.COURSE_ID=C.ID" +
                " LEFT JOIN TEACHER D ON B.TEACHER_ID=D.ID" +
                " LEFT JOIN CLASSROOM E ON B.CLASSROOM_ID=E.ID" +
                " WHERE A.STUDENT_ID=" + studentId +
                " AND A.ELECTIVE_TERM=" + term +
                " AND A.ELECTIVE_SECTION=" + section +
                " AND YEAR(A.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'";
        return this.jdbcTemplate.query(sql, new StudentCurrentElectionRowMapper());
    }

    public int deleteStudentElectiveCourse(int studentId, int scheduleId, int term, int section, int week, int time) {
        String sql = "DELETE FROM STUDENT_COURSE_ELECTIVE " +
                "WHERE STUDENT_ID=" + studentId +
                " AND COURSE_SCHEDULING_ID=" + scheduleId +
                " AND ELECTIVE_TERM=" + term +
                " AND ELECTIVE_WEEK=" + week +
                " AND ELECTIVE_TIME=" + time +
                " AND ELECTIVE_SECTION=" + section +
                " AND YEAR(ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'";
        return this.jdbcTemplate.update(sql);
    }

    public int clearCourseElectiveData(int scheduleId, int term, int section, int week, int time) {
        String sql = "DELETE FROM STUDENT_COURSE_ELECTIVE " +
                "WHERE  COURSE_SCHEDULING_ID=" + scheduleId +
                " AND ELECTIVE_TERM=" + term +
                " AND ELECTIVE_WEEK=" + week +
                " AND ELECTIVE_TIME=" + time +
                " AND ELECTIVE_SECTION=" + section +
                " AND YEAR(ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'";
        return this.jdbcTemplate.update(sql);
    }

    public int isMoreElectiveNum(int courseSchedulingId, int term, int section, int week, int time) {
        String sql = "SELECT  " +
                "(SELECT COUNT(H.STUDENT_ID) " +
                "FROM STUDENT_COURSE_ELECTIVE H " +
                "WHERE H.COURSE_SCHEDULING_ID=A.COURSE_SCHEDULING_ID " +
                " AND H.ELECTIVE_TERM=" + term +
                " AND H.ELECTIVE_WEEK=" + week +
                " AND H.ELECTIVE_TIME=" + time +
                " AND H.ELECTIVE_SECTION=" + section +
                " AND YEAR(H.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "')<A.RESTRICTION_NUMBER " +
                "FROM COURSE_SCHEDULING A " +
                "WHERE A.COURSE_SCHEDULING_ID=" + courseSchedulingId;
        return this.jdbcTemplate.queryForInt(sql);
    }

    public List<ElectiveCourseItem> getElectiveCourse(int studentId, int term, int section, int week, int time) {

        String sql = "SELECT A.COURSE_SCHEDULING_ID,A.RESTRICTION_NUMBER,A.RESTRICTION_REQUIRED," +
                "A.COURSE_ID,C.COURSE_NAME,A.TEACHER_ID,D.TEACHER_NAME,E.CLASSROOM_LOCATION," +
                //当前选课人数
                "(SELECT COUNT(H.STUDENT_ID) FROM STUDENT_COURSE_ELECTIVE H " +
                "WHERE H.COURSE_SCHEDULING_ID=A.COURSE_SCHEDULING_ID" +
                " AND H.ELECTIVE_TERM=" + term +
                " AND H.ELECTIVE_WEEK=" + week +
                " AND H.ELECTIVE_TIME=" + time +
                " AND H.ELECTIVE_SECTION=" + section +
                " AND YEAR(H.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "') AS CURRENT_NUM  FROM COURSE_SCHEDULING A " +
                "LEFT JOIN COURSE_SCHEDULING_CLASS B ON A.COURSE_SCHEDULING_ID=B.COURSE_SCHEDULING_ID " +
                "LEFT JOIN COURSE C ON A.COURSE_ID=C.ID " +
                "LEFT JOIN TEACHER D ON A.TEACHER_ID=D.ID " +
                "LEFT JOIN CLASSROOM E ON A.CLASSROOM_ID=E.ID " +
                //班级限制
                "WHERE (B.CLASS_ID IS NULL OR B.CLASS_ID IN (" +
                "SELECT STUDY_CLASS_ID AS CLASS_ID FROM STUDENT WHERE ID=" + studentId +
                " UNION " +
                "SELECT ADMIN_CLASS_ID AS CLASS_ID FROM STUDENT WHERE ID=" + studentId +
                //学期限制
                ")) AND A.RESTRICTION_TERM&" + term + "=" + term +
                //周期限制
                " AND A.RESTRICTION_WEEK&" + week + "=" + week +
                //节次限制
                " AND A.RESTRICTION_TIME&" + time + "=" + time +
                //学段限制
                " AND A.RESTRICTION_SECTION&" + section + "=" + section +
                //学生已选课程
                " AND A.COURSE_SCHEDULING_ID NOT IN (" +
                "SELECT COURSE_SCHEDULING_ID FROM STUDENT_COURSE_ELECTIVE WHERE STUDENT_ID=" + studentId + ")" +
                //选课黑名单限制
                " AND (D.BLACK_LIST_ID IS NULL OR D.BLACK_LIST_ID NOT IN(" +
                "SELECT BLACK_LIST_ID FROM STUDENT_BLACK_LIST WHERE STUDENT_ID=" + studentId + "))" +
                //年级限制
                " AND (A.RESTRICTION_GRADE IS NULL OR A.RESTRICTION_GRADE='' OR A.RESTRICTION_GRADE=" +
                "(SELECT Y.CLASS_YEAR FROM STUDENT X LEFT JOIN CLASS Y ON X.STUDY_CLASS_ID=Y.ID WHERE X.ID=" + studentId + ")" +
                " OR A.RESTRICTION_GRADE=" +
                "(SELECT Y.CLASS_YEAR FROM STUDENT X LEFT JOIN CLASS Y ON X.ADMIN_CLASS_ID=Y.ID WHERE X.ID=" + studentId + "))" +
                //人数限制
                " AND A.RESTRICTION_NUMBER>(SELECT COUNT(I.STUDENT_ID) FROM STUDENT_COURSE_ELECTIVE I " +
                "WHERE I.COURSE_SCHEDULING_ID=A.COURSE_SCHEDULING_ID" +
                " AND I.ELECTIVE_TERM=" + term +
                " AND I.ELECTIVE_WEEK=" + week +
                " AND I.ELECTIVE_TIME=" + time +
                " AND I.ELECTIVE_SECTION=" + section +
                " AND YEAR(I.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "')" +
                " ORDER BY A.RESTRICTION_REQUIRED DESC";

        return this.jdbcTemplate.query(sql, new ElectiveCourseItemRowMapper());
    }

    public int addElectiveCourse(@PartialCacheKey int studentId, int scheduleId, @PartialCacheKey int term, @PartialCacheKey int section, @PartialCacheKey int week, @PartialCacheKey int time) {
        String sql = "SELECT COUNT(*) FROM STUDENT_COURSE_ELECTIVE " +
                " WHERE STUDENT_ID=" + studentId +
                " AND ELECTIVE_TERM=" + term +
                " AND ELECTIVE_SECTION=" + section +
                " AND ELECTIVE_TIME=" + time +
                " AND ELECTIVE_WEEK=" + week +
                " AND YEAR(ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'";
        int count = this.jdbcTemplate.queryForInt(sql);

        if (count == 0) {
            SimpleJdbcInsert insert = new SimpleJdbcInsert(this.jdbcTemplate);
            insert.withTableName("STUDENT_COURSE_ELECTIVE");
            insert.compile();

            Map<String, Object> args = new HashMap<String, Object>();
            args.put("STUDENT_ID", studentId);
            args.put("COURSE_SCHEDULING_ID", scheduleId);
            args.put("ELECTIVE_DATE", new Date(System.currentTimeMillis()));
            args.put("ELECTIVE_TERM", term);
            args.put("ELECTIVE_SECTION", section);
            args.put("ELECTIVE_TIME", time);
            args.put("ELECTIVE_WEEK", week);
            return insert.execute(args);
        }
        return 0;
    }

    public int updateElectiveCourse(int id, int scheduleId) {
        String sql = "UPDATE STUDENT_COURSE_ELECTIVE " +
                "SET COURSE_SCHEDULING_ID=" + scheduleId +
                ",ELECTIVE_DATE='" + new Date(System.currentTimeMillis()) +
                "' WHERE ID=" + id;
        return this.jdbcTemplate.update(sql);
    }

    public int deleteElectiveCourse(int id) {
        String sql = "DELETE FROM STUDENT_COURSE_ELECTIVE WHERE ID=" + id;
        return this.jdbcTemplate.update(sql);
    }

    private class ElectiveCourseItemRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            ElectiveCourseItem electiveCourseItem = new ElectiveCourseItem();
            electiveCourseItem.setCourseSchedulingId(resultSet.getInt("COURSE_SCHEDULING_ID"));
            electiveCourseItem.setCourseId(resultSet.getInt("COURSE_ID"));
            electiveCourseItem.setClassroomLocation(resultSet.getString("CLASSROOM_LOCATION"));
            electiveCourseItem.setTeacherId(resultSet.getInt("TEACHER_ID"));
            electiveCourseItem.setTeacherName(resultSet.getString("TEACHER_NAME"));
            electiveCourseItem.setCourseName(resultSet.getString("COURSE_NAME"));
            electiveCourseItem.setRestrictionNum(resultSet.getInt("RESTRICTION_NUMBER"));
            electiveCourseItem.setCurrentNum(resultSet.getInt("CURRENT_NUM"));
            electiveCourseItem.setRequired(resultSet.getBoolean("RESTRICTION_REQUIRED") ? 1 : 0);
            return electiveCourseItem;
        }
    }

    private class StudentCurrentElectionRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            StudentCourseElective studentCourseElective = new StudentCourseElective();
            studentCourseElective.setId(resultSet.getInt("ID"));
            studentCourseElective.setScheduleId(resultSet.getInt("COURSE_SCHEDULING_ID"));
            studentCourseElective.setCourseName(resultSet.getString("COURSE_NAME"));
            studentCourseElective.setTeacherName(resultSet.getString("TEACHER_NAME"));
            studentCourseElective.setElectiveTime(resultSet.getInt("ELECTIVE_TIME"));
            studentCourseElective.setElectiveWeek(resultSet.getInt("ELECTIVE_WEEK"));
            studentCourseElective.setClassroomLocation(resultSet.getString("CLASSROOM_LOCATION"));
            return studentCourseElective;
        }
    }

    private class ElectiveCourseSituationRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            ElectiveCourseSituation electiveCourseSituation = new ElectiveCourseSituation();
            electiveCourseSituation.setCurrentNum(resultSet.getInt("CURRENT_NUM"));
            electiveCourseSituation.setElectiveWeek(resultSet.getInt("ELECTIVE_WEEK"));
            electiveCourseSituation.setElectiveTime(resultSet.getInt("ELECTIVE_TIME"));
            electiveCourseSituation.setSchedulingId(resultSet.getInt("COURSE_SCHEDULING_ID"));
            return electiveCourseSituation;
        }
    }

    // 陈瑞军 添加
    public List<StudentCourseElectiveCredit> getStudentAllElection(int studentId, int term, int section, int type) {
        String sql = "SELECT A.*,C.*,D.* FROM STUDENT_COURSE_ELECTIVE A" +
                " LEFT JOIN COURSE_SCHEDULING B ON A.COURSE_SCHEDULING_ID=B.COURSE_SCHEDULING_ID" +
                " LEFT JOIN COURSE C ON B.COURSE_ID=C.ID" +
                " LEFT JOIN COURSE_STRUCTURE D ON C.COURSE_STRUCTURE_ID=D.COURSE_STRUCTURE_ID" +
                " WHERE A.STUDENT_ID=" + studentId + " AND D.STUDY_TYPE=" + type + " AND A.ELECTIVE_TERM = " + term + " AND A.ELECTIVE_SECTION = " + section;
        return (List<StudentCourseElectiveCredit>) this.jdbcTemplate.query(sql, new RowMapper<StudentCourseElectiveCredit>() {

            @Override
            public StudentCourseElectiveCredit mapRow(ResultSet resultSet, int i) throws SQLException {
                StudentCourseElectiveCredit sec = new StudentCourseElectiveCredit();
                sec.setIsGetCredit(resultSet.getInt("IS_OBTAIN_CREDITS"));
                Course course = new Course();
                course.setId(resultSet.getInt("ID"));
                course.setCourseName(resultSet.getString("COURSE_NAME"));
                course.setCourseCode(resultSet.getString("COURSE_CODE"));
                course.setCourseEvaluation(resultSet.getString("COURSE_EVALUATION"));
                course.setCourseContent(resultSet.getString("COURSE_CONTENT"));
                course.setCourseRequirement(resultSet.getString("COURSE_REQUIREMENT"));
                course.setCourseComment(resultSet.getString("COURSE_COMMENT"));
                course.setCourseNumber(resultSet.getString("COURSE_NUMBER"));
                course.setCoursePeriod(resultSet.getString("COURSE_PERIOD"));
                course.setStudyCredit(resultSet.getInt("STUDY_CREDIT"));
                if (null != resultSet.getString("COURSE_STRUCTURE_ID")) {
                    CourseStructure courseStructure = new CourseStructure();
                    courseStructure.setId(resultSet.getInt("COURSE_STRUCTURE_ID"));
                    courseStructure.setStudyArea(resultSet.getString("STUDY_AREA"));
                    courseStructure.setStudySubject(resultSet.getString("STUDY_SUBJECT"));
                    courseStructure.setStudyType(resultSet.getInt("STUDY_TYPE"));
                    course.setCourseStructure(courseStructure);
                }
                sec.setCourse(course);
                return sec;
            }
        });
    }

    /* 陈瑞军添加 获取某课程的全部学分情况 */
    private static final String QUERY_STUDENT_COURSE_CREDIT = "SELECT A.STUDENT_ID, A.IS_OBTAIN_CREDITS, B.STUDENT_NAME, B.STUDENT_NO FROM STUDENT_COURSE_ELECTIVE A LEFT JOIN STUDENT B ON A.STUDENT_ID = B.ID WHERE A.COURSE_SCHEDULING_ID = ?";

    @Override
    public List<StudentCourseCredit> getStudentCourseCredits(int schedulingId) {
        return (List<StudentCourseCredit>) this.jdbcTemplate.query(QUERY_STUDENT_COURSE_CREDIT, new Object[]{schedulingId}, new RowMapper<StudentCourseCredit>() {

            @Override
            public StudentCourseCredit mapRow(ResultSet resultSet, int i) throws SQLException {
                StudentCourseCredit scc = new StudentCourseCredit();
                scc.setStudentId(resultSet.getInt("STUDENT_ID"));
                scc.setStudentName(resultSet.getString("STUDENT_NAME"));
                scc.setStudentNumber(resultSet.getString("STUDENT_NO"));
                scc.setIsGetCredit(resultSet.getInt("IS_OBTAIN_CREDITS"));
                return scc;
            }
        });
    }
}
